package zsrt.com.excel;


import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import zsrt.com.dao.ExamineeDao;
import zsrt.com.dao.impl.ExamineeDaoImpl;

public class GetValue {
	
	/**
	 * 从excel表格取出数据到map
	 * @return
	 */
	public static Map<String,ExamineeDao> getValue() {
		Map<String,ExamineeDao> map = new HashMap<String,ExamineeDao>();
		Workbook readwb = null;
		try{
			//构建Workbook对象, 只读Workbook对象直接从本地文件创建Workbook
			InputStream instream = new FileInputStream("D:\\zhongshen\\workspace\\javase-examineeSystem\\data.xls");
			readwb = Workbook.getWorkbook(instream);
			// Sheet的下标是从0开始，获取第一张Sheet表
			Sheet readsheet = readwb.getSheet(0);
			// 获取Sheet表中所包含的总列数
			int rsColumns = readsheet.getColumns();
			// 获取Sheet表中所包含的总行数
			int rsRows = readsheet.getRows();
			// 获取指定单元格的对象引用
			int k = 1;
			String id = "";
			for (int i=0; i<rsRows; i++){
				ExamineeDao examinee = new ExamineeDaoImpl();
				for (int j=0; j<rsColumns; j++){
					Cell cell = readsheet.getCell(j, i);
					if(i > 0){
						if(j == 0){
							examinee.setId(cell.getContents());
							//拿到id，赋值给id，到时候拿来弄成键
							id = cell.getContents();
						}else{
							switch (k) {
							case 1:
								examinee.setPassword(cell.getContents());
								
								k++;
								break;
							case 2:
								examinee.setName(cell.getContents());
								k++;
								break;
							case 3:
								examinee.setAge(new Integer(cell.getContents()));
								k++;
								break;
							case 4:
								examinee.setProvince(cell.getContents());
								k++;
								break;
							case 5:
								examinee.setCity(cell.getContents());
								k++;
								break;
							case 6:
								examinee.setPhone(cell.getContents());
								k++;
								break;
							case 7:
								examinee.setChinese(new Double(cell.getContents()));
								k++;
								break;
							case 8:
								examinee.setMath(new Double(cell.getContents()));
								k++;
								break;
							case 9:
								examinee.setEnglish(new Double(cell.getContents()));
								k++;
								break;
							case 10:
								examinee.setTotal(new Double(cell.getContents()));
								k++;
								break;
							default:
								break;
							}
						}
					}
				}
				if(i > 0){
					map.put(id, examinee);
					k = 1;
				}
			}
			
			// 利用已经创建的Excel工作薄,创建新的可写入的Excel工作薄
			jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File("D:\\zhongshen\\workspace\\javase-examineeSystem\\data.xls"), readwb);
			// 写入Excel对象
			wwb.write();
			wwb.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			readwb.close();
		}
		return map;
	}
}
